package com.test.study.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

/**
 * DDBC 的 CRUD操作
 * @author huyong
 *
 */
public class JDBCCRUD {
	private static final Logger LOGGER = Logger.getLogger(JDBCCRUD.class.getName());
	private static Connection connection;
	private static Statement statement;
	private static ResultSet resultSet;

	@Before
	public void before() throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mp?characterEncoding=utf8&useUnicode=true", "root", "root");
		statement = connection.createStatement();

	}

	@Test
	public void save() throws SQLException {
		String sql = "insert into user_test values(null,'eee','123','勇哥',104)";
		int count = statement.executeUpdate(sql);
		LOGGER.info("save result=" + count);
	}

	@Test
	public void update() throws SQLException {
		String sql = "update user_test set password='!QAZ2wsx3edc' where id=5";
		long count = statement.executeLargeUpdate(sql);
		LOGGER.info("update result=" + count);
	}

	@Test
	public void find() throws SQLException {
		String sql = "select * from user_test";
		resultSet = statement.executeQuery(sql);
		while (resultSet.next()) {
			int id = resultSet.getInt("id");
			String username = resultSet.getString("username");
			String password = resultSet.getString("password");
			String nickname = resultSet.getString("nickname");
			int age = resultSet.getInt("age");
			LOGGER.info(id + "\t" + username + "\t" + password + "\t" + nickname + "\t" + age);
		}

	}

	@Test
	public void delete() throws SQLException {
		String sql = "delete from user_test where id=5";
		long count = statement.executeLargeUpdate(sql);
		LOGGER.info("delete result=" + count);
	}

	@After
	public void after() {
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (Exception e) {
				LOGGER.log(Level.WARNING, e.getMessage());
			}
			resultSet = null;
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (Exception e) {
				LOGGER.log(Level.WARNING, e.getMessage());
			}
			statement = null;
		}
		if (connection != null) {
			try {
				connection.close();
			} catch (Exception e) {
				LOGGER.log(Level.WARNING, e.getMessage());
			}
			connection = null;
		}

	}
}
